﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using DAO;
using DTO;
namespace BUS
{
    public class GiaoHangBUS
    {
        public static void DeleteGiaoHang(int maGiaoHang)
        {
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                // Tang so luong binh gas trong kho, dua vao so luong da giao
                String strSql = "select* from ChiTietXeDi where MaGiaoHang=" + maGiaoHang;
                DataTable dtChiTiet = DataAccess.Query(tran, strSql);
                foreach (DataRow aRow in dtChiTiet.Rows)
                {
                    int loaiGas = (int)aRow["LoaiGas"];
                    int soLuongGiao = (int)aRow["SoLuongGiao"];
                    BinhGasDAO.TangSoLuong(tran, loaiGas, soLuongGiao);
                }
                // Giam so luong vo trong kho, dua vao so luong thu hoi
                strSql = "select* from ThuHoi where MaGiaoHang=" + maGiaoHang;
                DataTable dtThuHoi = DataAccess.Query(tran, strSql);
                foreach (DataRow aRow in dtThuHoi.Rows)
                {
                    int loaiGas;
                    int soLuong = (int)aRow["SoLuong"];
                    if (aRow["VoThuVe"] != DBNull.Value)
                    {
                        loaiGas = (int)aRow["VoThuVe"];
                        VoBinhGasDAO.GiamSoLuong(tran, loaiGas, soLuong);
                    }
                }
                // Xoa chitietxedi
                strSql = "delete from ChiTietXeDi where MaGiaoHang=" + maGiaoHang;
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.ExecuteNonQuery();
                // Xoa phanphoi
                strSql = "delete from PhanPhoi where MaGiaoHang=" + maGiaoHang;
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.ExecuteNonQuery();
                // Xoa thuhoi
                strSql = "delete from ThuHoi where MaGiaoHang=" + maGiaoHang;
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.ExecuteNonQuery();
                // Delete GiaoHang
                GiaoHangDAO.DeleteGiaoHang(tran, maGiaoHang);
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw;
            }
        }
        public static void HoanThanhGiaoHang(GiaoHangDTO giaoHang, List<PhanPhoiDTO> dsPhanPhoi, List<ThuHoiDTO> dsThuHoi)
        {
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                // Them phan phoi
                foreach (PhanPhoiDTO phanPhoi in dsPhanPhoi)
                {
                    PhanPhoiDAO.InsertPhanPhoi(tran, phanPhoi);
                }
                // Them thu hoi
                foreach (ThuHoiDTO thuHoi in dsThuHoi)
                {
                    ThuHoiDAO.InsertThuHoi(tran, thuHoi);
                }
                // Cap nhat SoLuongGiao cua ChiTietXeDi, tang so binh Gas con du
                String strSql = "select* from ChiTietXeDi where MaGiaoHang=" + giaoHang.Id;
                DataTable dtChiTiet = new DataTable();
                dtChiTiet = DataAccess.Query(tran, strSql);
                DataTable dtPhanPhoi = new DataTable();
                strSql = "select LoaiGas, sum(SoLuong) as SoLuongGiao from PhanPhoi where MaGiaoHang=" + giaoHang.Id + " group by LoaiGas";
                dtPhanPhoi = DataAccess.Query(tran, strSql);
                if (dtChiTiet.Rows.Count > 0 && dtPhanPhoi.Rows.Count > 0)
                {
                    foreach (DataRow aRow in dtChiTiet.Rows)
                    {
                        foreach (DataRow bRow in dtPhanPhoi.Rows)
                        {
                            if (aRow["LoaiGas"].Equals(bRow["LoaiGas"]) == true)
                            {
                                int soLuong = (int)aRow["SoLuong"];
                                int soLuongGiao = int.Parse(bRow["SoLuongGiao"].ToString());
                                ChiTietXeDi chiTietXeDi = new ChiTietXeDi();
                                chiTietXeDi.ID = (int)aRow["ID"];
                                chiTietXeDi.MaGiaoHang = giaoHang.Id;
                                chiTietXeDi.MaLoaiGas = (int)aRow["LoaiGas"];
                                chiTietXeDi.SoLuong = soLuong;
                                chiTietXeDi.SoLuongGiao = soLuongGiao;
                                GiaoHangDAO.UpdateChiTietXeDi(tran, chiTietXeDi);
                                int soDu = soLuong - soLuongGiao;
                                if (soDu > 0)
                                {
                                    BinhGasDAO.TangSoLuong(tran, (int)bRow["LoaiGas"], soDu);
                                }
                            }
                        }
                    }
                }
                // Dat trang thai GiaoHang la HoanThanh = true
                giaoHang.HoanThanh = true;
                GiaoHangDAO.UpdateGiaoHang(tran, giaoHang);
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw;
            }
        }
        
        public static void Delete(int id)
        {
            OleDbConnection con = DataAccess.OpenOleConnection();
            OleDbTransaction tran = con.BeginTransaction();
            try
            {                
                String strSql = "select ID from ChiTietXeDi where MaGiaoHang =" + id;
                DataTable dt = DataAccess.Query(tran, strSql);
                foreach (DataRow row in dt.Rows)
                {
                    int ma = (int)row["ID"];
                    GiaoHangDAO.DeleteChiTietXeDi(tran, ma);
                }
                strSql = "select ID from PhanPhoi where MaGiaoHang=" + id;
                dt = DataAccess.Query(tran, strSql);
                foreach (DataRow row in dt.Rows)
                {
                    int ma = (int)row["ID"];
                    PhanPhoiDAO.DeletePhanPhoi(tran, ma);
                }
                strSql = "select ID from ThuHoi where MaGiaoHang=" + id;
                dt = DataAccess.Query(tran, strSql);
                foreach (DataRow row in dt.Rows)
                {
                    int ma = (int)row["ID"];
                    ThuHoiDAO.DeleteThuHoi(tran, id);
                }
                GiaoHangDAO.DeleteGiaoHang(tran, id);
                tran.Commit();
                con.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                con.Close();
                throw;
            }
        }
        public static void InsertChiTietXeDi(ChiTietXeDi ctXeDi)
        {
            OleDbConnection con = DataAccess.OpenOleConnection();
            OleDbTransaction tran = con.BeginTransaction(IsolationLevel.ReadCommitted);            
            try
            {
                DataTable dt = BinhGasDAO.Tim(tran, ctXeDi.MaLoaiGas);
                int SoLuong = (int)dt.Rows[0]["SoLuong"];
                if (SoLuong < ctXeDi.SoLuong)
                {
                    throw new Exception("Số lượng Gas vượt quá lượng có trong kho");
                }
                BinhGasDAO.GiamSoLuong(tran, ctXeDi.MaLoaiGas, ctXeDi.SoLuong);
                GiaoHangDAO.InsertChiTietXeDi(tran, ctXeDi);
                tran.Commit();
                con.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                con.Close();
                throw ex;
            }
        }
        public static void UpdateChiTietXeDi(ChiTietXeDi ctXeDi)
        {
            OleDbConnection con = DataAccess.OpenOleConnection();
            OleDbTransaction tran = con.BeginTransaction();
            String strSql = "select * from ChiTietXeDi where ID=" + ctXeDi.ID.ToString();
            DataTable dt = DataAccess.Query(strSql);
            ChiTietXeDi temp = new ChiTietXeDi();
            int LoaiGas = (int)dt.Rows[0]["LoaiGas"];
            int SoLuong = (int)dt.Rows[0]["SoLuong"];            
          
            try
            {
                BinhGasDAO.TangSoLuong(tran, LoaiGas, SoLuong);
                dt = BinhGasDAO.Tim(tran, ctXeDi.MaLoaiGas);
                SoLuong = (int)dt.Rows[0]["SoLuong"];
                if (SoLuong < ctXeDi.SoLuong)
                {
                    throw new Exception("Số lượng Gas vượt quá lượng có trong kho");
                }
                BinhGasDAO.GiamSoLuong(tran, ctXeDi.MaLoaiGas, ctXeDi.SoLuong);
                GiaoHangDAO.UpdateChiTietXeDi(tran, ctXeDi);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
        }
        public static void DeleteChiTietXeDi(int id)
        {
            OleDbConnection con = DataAccess.OpenOleConnection();
            OleDbTransaction tran = con.BeginTransaction();
            ChiTietXeDi temp = new ChiTietXeDi();
            String strSql = "select * from ChiTietXeDi where ID=" + id.ToString();
            DataTable dt = DataAccess.Query(tran, strSql);
            int LoaiGas = (int)dt.Rows[0]["LoaiGas"];
            int SoLuong = (int)dt.Rows[0]["SoLuong"];
            try
            {
                BinhGasDAO.TangSoLuong(tran, LoaiGas, SoLuong);
                GiaoHangDAO.DeleteChiTietXeDi(tran, id);
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
        }
        
    }
}
